package edu.pe.cayetano.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import edu.pe.cayetano.framework.Configuration;
import edu.pe.cayetano.framework.DAOException;
import edu.pe.cayetano.model.bean.AlumnoBean;

public class AlumnoDAO  extends BaseDAO{

	public AlumnoDAO(Connection cnx) {
		super(cnx);
	}
	
	/**
	 * Metodo que retorna el listado de ciclos 
	 * */
	public ArrayList<AlumnoBean> getAlumnosxCiclo(String nivel) throws Exception{

		AlumnoBean					alumnoTO 	= null;
		ArrayList<AlumnoBean> 		lista		= new ArrayList<AlumnoBean>();
		PreparedStatement   		pstmt 		= null;
		ResultSet 					rs 			= null;
		String 						schema		= Configuration.getSchemaDB();
	
		try{
			StringBuffer query  = new StringBuffer();
			query.append("SELECT CODIGO_ALUMNO,NOMBRE_COMPLETO FROM "+schema+"V_ALUMNO WHERE CICLO_ID=?");
			pstmt = getConnection().prepareStatement(query.toString());
			pstmt.setString(1,nivel);
			rs=pstmt.executeQuery();
			
			alumnoTO=new AlumnoBean();
			alumnoTO.setCodigo("0");
			alumnoTO.setNombre("");
			
			lista.add(alumnoTO);
			while(rs.next()){
				alumnoTO=new AlumnoBean();
				alumnoTO.setCodigo(rs.getString(1));
				alumnoTO.setNombre(rs.getString(2));
				lista.add(alumnoTO);
			}
		}catch(Exception e){
			throw new DAOException(e.getMessage());
		}finally{
			try{
				try{pstmt.close();}catch(Exception e){}
				try{rs.close();}catch(Exception e){}
			}catch(Exception e1){}
		}
		return lista;
	}
	
	
	/**
	 * Metodo que retorna el listado de ciclos 
	 * */
	public ArrayList<AlumnoBean> getAlumnosxCicloxCombo(String nivel) throws Exception{

		AlumnoBean			alumnoTO 	= null;
		ArrayList<AlumnoBean> 			lista		= new ArrayList<AlumnoBean>();
		PreparedStatement   pstmt 		= null;
		ResultSet 			rs 			= null;
		String 				schema		= Configuration.getSchemaDB();
	
		try{
			StringBuffer query  = new StringBuffer();
			query.append("SELECT CODIGO_ALUMNO,APEPATERNO,NOMBRE FROM "+schema+"ALUMNO WHERE CICLO_ID=?");
			pstmt = getConnection().prepareStatement(query.toString());
			pstmt.setString(1,nivel);
			rs=pstmt.executeQuery();
			
			alumnoTO=new AlumnoBean();
			alumnoTO.setCodigo("0");
			alumnoTO.setNombre("");
			
			lista.add(alumnoTO);
			while(rs.next()){
				alumnoTO=new AlumnoBean();
				alumnoTO.setCodigo(rs.getString(1));
				if(rs.getString(1).equalsIgnoreCase("0"))
					alumnoTO.setNombre(rs.getString(2));
				else
					alumnoTO.setNombre(rs.getString(2)+", "+rs.getString(3));
				lista.add(alumnoTO);
			}
		}catch(Exception e){
			throw new DAOException(e.getMessage());
		}finally{
			try{
				try{pstmt.close();}catch(Exception e){}
				try{rs.close();}catch(Exception e){}
			}catch(Exception e1){}
		}
		return lista;
	}
}
